package com.hst.web.sys.web;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.fasterxml.jackson.core.JsonGenerationException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.hst.core.annotation.WebAuth;
import com.hst.core.dao.IDService;
import com.hst.core.dao.IORDao;
import com.hst.core.dao.ISQLDao;
import com.hst.core.dao.ORQuery;
import com.hst.core.dao.ORQuery.Op;
import com.hst.web.base.entity.TAccountIdentity;
import com.hst.web.base.entity.TCompanyContractor;
import com.hst.web.base.entity.TCompanyInfo;
import com.hst.web.base.entity.TContractorInfo;
import com.hst.web.base.entity.TVehicleInfo;

/**
 * @author duyog
 *
 */
@WebAuth
@Controller
@RequestMapping("/dataMigration")
public class DataMigrationController {
	String fold = "../filestore";
	
	@Autowired
	IDService id;
	
	@Autowired
	IORDao dao;
	@Autowired
	ISQLDao sql;


	//private static String url = "jdbc:oracle:thin:@172.16.1.85/scip";		//用户测试库
	//private static String url = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";		//本地数据库
	private static String url = "jdbc:oracle:thin:@172.21.167.12/orcl";			//用户生产库
	private static String user = "scip";
	private static String password = "scip";
	private static String driverClass = "oracle.jdbc.driver.OracleDriver";
	
	static {
		try {
			Class.forName(driverClass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	@RequestMapping("/readAccountIdentity")
	public void readAccountIdentity(Model m) throws JsonGenerationException, JsonMappingException, IOException {
		readAccountIdentity();
		m.addAttribute("msg","success");
	}
	
	@RequestMapping("/readVehicleInfo")
	public void readVehicleInfo(Model m) throws JsonGenerationException, JsonMappingException, IOException {
		readVehicleInfo();
		//读取车辆审批表中的照片
		readAskpassVehicleInfo();
		m.addAttribute("msg","success");
	}
	
	@RequestMapping("/readCompanyInfo")
	public void readCompanyInfo(Model m) throws JsonGenerationException, JsonMappingException, IOException {
		readCompanyInfo();
		m.addAttribute("msg","success");
	}
	
	@RequestMapping("/readContractorInfo")
	public void readContractorInfo(Model m) throws JsonGenerationException, JsonMappingException, IOException {
		readContractorInfo();
		m.addAttribute("msg","success");
	}
	
	//读取人员认证信息中的照片
	public void readAccountIdentity() {
		String path = "employeeface";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			//String sql = "select * from t_account_identity where not dynamicface_img is null";
			
			String sql = "select L.* from scip.t_account_identity L left join t_account_identity@scip R on L.username = R.username " + 
					"where not L.dynamicface_img is null and R.dynamic_face is null";
			stmt = conn.prepareStatement(sql);
			//stmt.setString(1,"赵晓峰");		//参数
			rs = stmt.executeQuery();
			while (rs.next()){
				//我的查询结果的第四列是BLOB对象
				String userName = rs.getString(1);

				TAccountIdentity accountIdentity= dao.get(TAccountIdentity.class, userName);
				if (accountIdentity != null) {
					String dynamicFace = accountIdentity.getDynamicFace();
					if (dynamicFace == null) {
						Blob blob = rs.getBlob(11);
						
						String name = getFileName(path, path + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob.getBytes(1, (int)blob.length()));
						out.close();
						
						accountIdentity.setDynamicFace(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(accountIdentity);					
					}
				}
			}
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(conn != null)  {
					conn.close();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}
	}
	
	
	
	//读取承包商信息中的照片
	public void readContractorInfo() {
		List<ORQuery> querys = new ArrayList<ORQuery>();
		String contractImgPath = "contractImg";
		String contractexpImgPath = "contractexpImg";
		String contractStampImgPath = "contractStampImg";
		String vRdTransportImgPath = "vRdTransportImg";
		String transportCertPath = "transportCert";
		 
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			//String sql = "select * from t_vehicle_info where owner_name=?";
			String sql = "select * from t_company_contractor";
			stmt = conn.prepareStatement(sql);
			//stmt.setString(1,"孙正武");		//参数
			rs = stmt.executeQuery();
			while (rs.next()){
				String companyCode = rs.getString(1);
				String contractorCode = rs.getString(2);
				
				Blob blob3 = rs.getBlob(3);		//contractImg
				Blob blob4 = rs.getBlob(4);		//contractexpImg
				Blob blob6 = rs.getBlob(6);		//contractStampImg
				Blob blob7 = rs.getBlob(7);		//vRdTransportImg
				
				querys.clear();
				querys.add(new ORQuery(Op.eq, "companyCode", companyCode));
				querys.add(new ORQuery(Op.eq, "contractorCode", contractorCode));
				List<TCompanyContractor> companyContractorList= dao.list(TCompanyContractor.class, querys);
				
				for (TCompanyContractor companyContractor: companyContractorList) {
					String contractImg = companyContractor.getContractImg();
					if (contractImg == null && (blob3 != null)) {
						String name = getFileName(contractImgPath, contractImgPath + ".png");
						name = generatePathName(name);
						
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob3.getBytes(1, (int)blob3.length()));
						out.close();
						
						companyContractor.setContractImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(companyContractor);
					}
					String contractexpImg = companyContractor.getContractexpImg();
					if (contractexpImg == null && (blob4 != null)) {
						String name = getFileName(contractexpImgPath, contractexpImgPath + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob4.getBytes(1, (int)blob4.length()));
						out.close();
						
						companyContractor.setContractexpImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(companyContractor);
					}
					String contractStampImg = companyContractor.getContractStampImg();
					if (contractStampImg == null && (blob6 != null)) {
						String name = getFileName(contractStampImgPath, contractStampImgPath + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob6.getBytes(1, (int)blob6.length()));
						out.close();
						
						companyContractor.setContractStampImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(companyContractor);
					}
				}
				
				querys.clear();
				querys.add(new ORQuery(Op.eq, "contractorCode", contractorCode));
				List<TContractorInfo> contractorInfoList= dao.list(TContractorInfo.class, querys);
				
				for (TContractorInfo contractorInfo: contractorInfoList) {
					String vrdtransportImg = contractorInfo.getVrdtransportImg();
					if (vrdtransportImg == null && (blob7 != null)) {
						String name = getFileName(vRdTransportImgPath, vRdTransportImgPath + ".png");
						name = generatePathName(name);

						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob7.getBytes(1, (int)blob7.length()));
						out.close();
						
						contractorInfo.setVrdtransportImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(contractorInfo);
					}
				}				
			}
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(conn != null)  {
					conn.close();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}
		
		//取得承包商transportcert(单位道路运输经营许可证照片)字段数据
		try {
			conn = getConnection();
			String sql = "select * from t_contractor_info where not transportcert is null";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()){
				String contractorCode = rs.getString(1);
				Blob blob17 = rs.getBlob(17);		//contractImg
				querys.clear();
				querys.add(new ORQuery(Op.eq, "contractorCode", contractorCode));
				List<TContractorInfo> contractorInfoList= dao.list(TContractorInfo.class, querys);
				
				for (TContractorInfo contractorInfo: contractorInfoList) {
					String transportCert = contractorInfo.getTransportCert();
					if (transportCert == null && (blob17 != null)) {
						String name = getFileName(transportCertPath, transportCertPath + ".png");
						name = generatePathName(name);
						
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob17.getBytes(1, (int)blob17.length()));
						out.close();
						
						contractorInfo.setTransportCert(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(contractorInfo);
					}
				}
			}
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(conn != null)  {
					conn.close();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}		
	}	
	
	
	//读取企业信息中的照片
	public void readCompanyInfo() {
		String path = "transportCert";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			//String sql = "select * from t_company_info where fullname=?";
			String sql = "select * from t_company_info where not transportcert is null";
			stmt = conn.prepareStatement(sql);
			//stmt.setString(1,"赵晓峰");		//参数
			rs = stmt.executeQuery();
			while (rs.next()){
				String companyCode = rs.getString(1);
				
				TCompanyInfo companyInfo= dao.get(TCompanyInfo.class, companyCode);
				if (companyInfo != null) {
					String transportCert = companyInfo.getTransportCert();
					if (transportCert == null) {
						//我的查询结果的第九列是BLOB对象
						Blob blob = rs.getBlob(9);
						
						String name = getFileName(path, path + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob.getBytes(1, (int)blob.length()));
						out.close();
						
						companyInfo.setTransportCert(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(companyInfo);
					}
				}
			}
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(conn != null)  {
					conn.close();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}
	}		
	
	//读取车辆信息中的照片
	public void readVehicleInfo() {
		List<ORQuery> querys = new ArrayList<ORQuery>();
		String rdTransportImgPath = "vRdTransportImg";
		String policyImgPath = "policyImg";
		String relationImgPath = "relationImg";
		String frontPhotoPath = "frontPhoto";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			//String sql = "select * from t_vehicle_info where owner_name=?";
			//String sql = "select * from t_vehicle_info";
			
			String sql = "select L.* from scip.t_vehicle_info L left join t_vehicle_info@scip R on L.plate_no = R.plate_no and L.engine_no = R.engine_no " +
					"where ((not L.v_rdtransport_img is null and R.v_Rd_Transport_img is null) or (not L.policy_img is null and R.policy_img is null) " +
					"or (not L.relation_img is null and R.relation_img is null) or (not L.front_photo is null and R.front_photo is null))";

			stmt = conn.prepareStatement(sql);
			//stmt.setString(1,"孙正武");		//参数
			rs = stmt.executeQuery();
			while (rs.next()){
				//我的查询结果的第四列是BLOB对象
				String plate_no = rs.getString(3);
				String engine_no = rs.getString(5);
				
				Blob blob11 = rs.getBlob(11);	//v_rdtransport_img
				Blob blob14 = rs.getBlob(14);	//policy_img
				Blob blob15 = rs.getBlob(15);	//relation_img
				Blob blob19 = rs.getBlob(19);	//front_photo
				
				querys.clear();
				querys.add(new ORQuery(Op.eq, "plateNo", plate_no));
				querys.add(new ORQuery(Op.eq, "engineNo", engine_no));
				List<TVehicleInfo> vehicleInfoList= dao.list(TVehicleInfo.class, querys);
				
//				if (vehicleInfoList.size() > 1) {
//					System.out.print("aaa");
//				}
				for (TVehicleInfo vehicleInfo: vehicleInfoList) {
					String vrdTransportImg = vehicleInfo.getVrdTransportImg();
					if (vrdTransportImg == null && (blob11 != null)) {
						String name = getFileName(rdTransportImgPath, rdTransportImgPath + ".png");
						name = generatePathName(name);
						
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob11.getBytes(1, (int)blob11.length()));
						out.close();
						
						vehicleInfo.setVrdTransportImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}
					String policyImg = vehicleInfo.getPolicyImg();
					if (policyImg == null && (blob14 != null)) {
						String name = getFileName(policyImgPath, policyImgPath + ".png");
						name = generatePathName(name);
						
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob14.getBytes(1, (int)blob14.length()));
						out.close();
						
						vehicleInfo.setPolicyImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}
					String relationImg = vehicleInfo.getRelationImg();
					if (relationImg == null && (blob15 != null)) {
						String name = getFileName(relationImgPath, relationImgPath + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob15.getBytes(1, (int)blob15.length()));
						out.close();
						
						vehicleInfo.setRelationImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}
					String frontPhoto = vehicleInfo.getFrontPhoto();
					if (frontPhoto == null && (blob19 != null)) {
						String name = getFileName(frontPhotoPath, frontPhotoPath + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob19.getBytes(1, (int)blob19.length()));
						out.close();
						
						vehicleInfo.setFrontPhoto(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}	
				}
			}
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(conn != null)  {
					conn.close();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}
	}
	
	
	//读取车辆审批表中的照片
	public void readAskpassVehicleInfo() {
		List<ORQuery> querys = new ArrayList<ORQuery>();
		String prdTransportImgPath = "pRdTransportImg";
		String vrdTransportImgPath = "vRdTransportImg";
		String policyImgPath = "policyImg";
		String relationImgPath = "relationImg";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			
			String sql = "select L.* from scip.t_askpass_detail_veh L left join t_vehicle_info@scip R on L.plate_no = R.plate_no and L.engine_no = R.engine_no " +
					"where ((not L.p_rdtransport_img is null and R.p_rdtransport_img is null) or (not L.v_rdtransport_img is null and R.v_Rd_Transport_img is null) " +
					"or (not L.policy_img is null and R.policy_img is null) or (not L.relation_img is null and R.relation_img is null))";

			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()){
				//我的查询结果的第四列是BLOB对象
				String plate_no = rs.getString(12);
				String engine_no = rs.getString(14);
				
				Blob blob10 = rs.getBlob(10);	//p_rdtransport_img
				Blob blob20 = rs.getBlob(20);	//v_rdtransport_img
				Blob blob23 = rs.getBlob(23);	//policy_img
				Blob blob24 = rs.getBlob(24);	//relation_img
				
				querys.clear();
				querys.add(new ORQuery(Op.eq, "plateNo", plate_no));
				querys.add(new ORQuery(Op.eq, "engineNo", engine_no));
				List<TVehicleInfo> vehicleInfoList= dao.list(TVehicleInfo.class, querys);

				for (TVehicleInfo vehicleInfo: vehicleInfoList) {
					String prdTransportImg = vehicleInfo.getPrdTransportImg();
					if (prdTransportImg == null && (blob10 != null)) {
						String name = getFileName(prdTransportImgPath, prdTransportImgPath + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob10.getBytes(1, (int)blob10.length()));
						out.close();
						
						vehicleInfo.setPrdTransportImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}						
					String vrdTransportImg = vehicleInfo.getVrdTransportImg();
					if (vrdTransportImg == null && (blob20 != null)) {
						String name = getFileName(vrdTransportImgPath, vrdTransportImgPath + ".png");
						name = generatePathName(name);
						
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob20.getBytes(1, (int)blob20.length()));
						out.close();
						
						vehicleInfo.setVrdTransportImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}
					String policyImg = vehicleInfo.getPolicyImg();
					if (policyImg == null && (blob23 != null)) {
						String name = getFileName(policyImgPath, policyImgPath + ".png");
						name = generatePathName(name);
						
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob23.getBytes(1, (int)blob23.length()));
						out.close();
						
						vehicleInfo.setPolicyImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}
					String relationImg = vehicleInfo.getRelationImg();
					if (relationImg == null && (blob24 != null)) {
						String name = getFileName(relationImgPath, relationImgPath + ".png");
						name = generatePathName(name);
						
						//根据自定义的文件名在本地创建空文件,本地创建的文件后缀要与事先
						//保存的文件吻合
						File f = new File(fold + name);
						f.getParentFile().mkdirs();
						
						OutputStream out = null;
						//初始化字节流
						out = new FileOutputStream(f);
						//以字节流的形式将BLOB对象写入创建的文件  "f"
						out.write(blob24.getBytes(1, (int)blob24.length()));
						out.close();
						
						vehicleInfo.setRelationImg(name);	//employeeface/20191218/19CJ000001.jpg
						dao.update(vehicleInfo);
					}

				}
			}
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(conn != null)  {
					conn.close();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 获取数据库连接
	 */
	public static Connection getConnection(){
		try {
			Connection conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	
	String getFileName(String t, String fileName) {
		String sidName = "file-" + t;
		String sid = id.formatCode(10, id.getCode(sidName, true), true, "");

		int p = fileName.lastIndexOf('.');
		String ext = p > -1 ? fileName.substring(p) : ".";

		return t + "/" + sid.substring(4, 7) +  "/" + sid + ext;
	}
	
	DateFormat df = new SimpleDateFormat("yyyyMMdd");
	
	/**
	 * 加日期文件夹
	 * 
	 * @param path
	 * @return
	 */
	String generatePathName(String path) {
		int i = path.indexOf("/");
		path = path.substring(0, i + 1) + df.format(new Date()) + "/" + path.substring(i + 1);
		return path;
	}	
}
